# _*_ coding:utf-8 _*_
# @File  : spot_price.py
# @Time  : 2021-07-16  10:11
# @Author: zizle

# 现货价格数据分析
import datetime

import numpy as np
import pandas as pd
from fastapi import APIRouter, Depends, Query
from hutool.tools import datetime_handler
from db import FAConnection
from interfaces.depends import verify_date
from status import r_status
from global_constant import VARIETY

spot_api = APIRouter()


@spot_api.get('/', summary='指定日期的现货价格分析(基差,均价等)')
async def analysis_spot_price(date: str = Query(None)):
    sys_newest = True  # 查询系统最新日期
    if date:
        try:
            date = datetime.datetime.strptime(date, '%Y-%m-%d')
            sys_newest = False
        except ValueError:
            date = datetime.datetime.today()
    else:
        date = datetime.datetime.today()
    current_year = date.year
    # spot_start = datetime.datetime.strptime(f'{current_year}-01-01', '%Y-%m-%d')
    spot_start = datetime.datetime.strptime('2022-12-30', '%Y-%m-%d')
    spot_end = datetime.datetime.strptime(f'{current_year}-12-31', '%Y-%m-%d')
    db = FAConnection(conn_name='SpotAnalysis')

    # 查询最新的主力合约数据
    if sys_newest:
        query_dominant = """
            SELECT a.quotes_ts,a.variety_en,a.contract,a.close_price 
            FROM dat_futures_daily_quotes AS a
            INNER JOIN ruizy_variety_contract AS b 
            ON b.activity=0 AND b.contract_date=(SELECT MAX(contract_date) FROM ruizy_variety_contract)
            AND a.quotes_ts=(SELECT MAX(quotes_ts) FROM dat_futures_daily_quotes) 
            AND a.contract=CONCAT(b.variety_en,b.lcontract);
        """
        dominant_param = None
    else:
        query_dominant = """
            SELECT a.quotes_ts,a.variety_en,a.contract,a.close_price 
            FROM dat_futures_daily_quotes AS a
            INNER JOIN ruizy_variety_contract AS b 
            ON b.activity=0 AND b.contract_date=%s
            AND a.quotes_ts=%s 
            AND a.contract=CONCAT(b.variety_en,b.lcontract);
        """
        dominant_param = [date.strftime('%Y%m%d'), int(date.timestamp())]
    # 查询今年以来的所有现货价格数据
    query_spot = 'SELECT spot_ts,variety_en,price,src_note ' \
                 'FROM dat_spot_price WHERE spot_ts>=%s AND spot_ts<=%s;'

    dominant = db.query(query_dominant, dominant_param, keep_conn=True)
    # 对主力合约数据添加品种名称
    for r in dominant:
        r['variety_name'] = VARIETY.VARIETY_NAMES.get(r['variety_en'], r['variety_en'])
    spots = db.query(query_spot, [int(spot_start.timestamp()), int(spot_end.timestamp())])
    dominant_df = pd.DataFrame(dominant)
    spots_df = pd.DataFrame(spots)
    # spots_df = spots_df[spots_df['price'] > 0]
    # 将现货数据为0的改为np.nan
    spots_df['price'] = spots_df['price'].apply(lambda x: np.NAN if x == 0 else x)
    if dominant_df.empty:
        return {'code': r_status.SUCCESS, 'message': '分析期现数据成功,没有主力合约价格!',
                'query_date': f'{date.strftime("%Y-%m-%d")}', 'data': []}
    if spots_df.empty:
        return {'code': r_status.SUCCESS, 'message': '分析期现数据成功,没有现货价格数据!',
                'query_date': f'{date.strftime("%Y-%m-%d")}', 'data': []}
    dominant_df['date'] = dominant_df['quotes_ts'].apply(
        lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))
    spots_df['date'] = spots_df['spot_ts'].apply(lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))

    # 处理现货价的数据
    # 1 截取最大到主力合约的日期
    spots_df = spots_df[spots_df['spot_ts'] <= dominant_df.at[0, 'quotes_ts']]
    # 2 以品种分组处理出平均价开始 ----------
    # 2.1 取本月数据得本月均价
    current_month = date.month
    month_start, month_end = datetime_handler.get_current_month_start_end(current_year, current_month)
    month_df = spots_df[(spots_df['spot_ts'] >= int(month_start.timestamp())) & (
            spots_df['spot_ts'] <= int(month_end.timestamp()))].copy()
    month_avg = month_df.groupby(by=['variety_en'], as_index=False)['price'].mean()
    month_avg.rename(columns={'price': 'month_avg'}, inplace=True)
    # print(month_avg)
    del month_df
    # 2.2 取本季度数据得本季度均价
    # t = datetime.datetime.strptime('2021-02-12', '%Y-%m-%d')
    # quarter_start, quarter_end = datetime_handler.get_current_quarter_start_end(datetime.datetime.today())
    quarter_start, quarter_end = datetime_handler.get_current_quarter_start_end(date)
    quarter_df = spots_df[(spots_df['spot_ts'] >= int(quarter_start.timestamp())) & (
            spots_df['spot_ts'] <= int(quarter_end.timestamp()))].copy()
    quarter_avg = quarter_df.groupby(by=['variety_en'], as_index=False)['price'].mean()
    quarter_avg.rename(columns={'price': 'quarter_avg'}, inplace=True)
    # 2.3 计算本年度均价(默认查询为年度数据)
    current_year_start_ts = int(datetime.datetime.strptime(f'{current_year}-01-01', '%Y-%m-%d').timestamp())
    year_avg = spots_df[spots_df['spot_ts'] >= current_year_start_ts].groupby(by=['variety_en'], as_index=False)['price'].mean()
    year_avg.rename(columns={'price': 'year_avg'}, inplace=True)
    # 2.4 合并均价数据框
    avg_df = pd.merge(year_avg, quarter_avg, on=['variety_en'], how='left')
    avg_df = pd.merge(avg_df, month_avg, on=['variety_en'], how='left')
    # 2 处理平均价结束 ------------
    # 3 取最后两日的数据计算涨跌开始 -------
    spots_ts_list = list(spots_df.groupby(by=['spot_ts']).groups)
    spots_ts_list.sort()
    ts_start, ts_end = spots_ts_list[-2:] if len(spots_ts_list) > 1 else (spots_ts_list[0], spots_ts_list[0])
    temp_zddf = spots_df[(spots_df['spot_ts'] >= ts_start) & (spots_df['spot_ts'] <= ts_end)].copy()
    temp_zddf.sort_values(by=['variety_en', 'spot_ts'], inplace=True)
    # 3.2 以品种分开计算拼合数据
    zd_df = pd.DataFrame(columns=['spot_ts', 'variety_en', 'price', 'date', 'zd_value'])
    for variety_en in temp_zddf.groupby(by=['variety_en']).groups:
        group_df = temp_zddf[temp_zddf['variety_en'] == variety_en]
        group_df['zd_value'] = group_df['price'] - group_df['price'].shift(1)
        zd_df = pd.concat([zd_df, group_df])
    zd_df = zd_df[zd_df['spot_ts'] >= dominant_df.at[0, 'quotes_ts']]
    # 4 合并数据
    ret_df = pd.merge(dominant_df, zd_df[['variety_en', 'price', 'zd_value', 'src_note']], on=['variety_en'],
                      how='left')
    ret_df = pd.merge(ret_df, avg_df, on=['variety_en'], how='left')
    # 5 计算基差
    ret_df['basis'] = ret_df['price'] - ret_df['close_price']
    # 6 保留结果小数
    ret_df['zd_value'] = ret_df['zd_value'].apply(lambda x: round(x, 2))
    ret_df['month_avg'] = ret_df['month_avg'].apply(lambda x: round(x, 2))
    ret_df['quarter_avg'] = ret_df['quarter_avg'].apply(lambda x: round(x, 2))
    ret_df['year_avg'] = ret_df['year_avg'].apply(lambda x: round(x, 2))
    ret_df['basis'] = ret_df['basis'].apply(lambda x: round(x, 2))
    ret_df.fillna('-', inplace=True)
    ret_df = ret_df[['date', 'variety_en', 'variety_name', 'close_price', 'basis', 'price', 'zd_value', 'month_avg',
                     'quarter_avg', 'year_avg', 'src_note']]

    ret_data = {item['variety_en']: item for item in ret_df.to_dict(orient='records')}
    # 处理顺序(控制品种)
    nan_spot = {col: '-' for col in ret_df.columns.tolist()}
    sorted_variety = ['NI', 'CU', 'ZN', 'PB', 'AL', 'SN', 'AU', 'AG', 'SS', 'RB', 'HC', 'BU', 'RU', 'SP', 'A', 'M', 'Y',
                      'P', 'LH', 'JD', 'C', 'CS', 'I', 'J', 'JM', 'SC', 'FU', 'LU', 'L', 'PP', 'V', 'EB', 'PG', 'EG',
                      'TA', 'PF', 'MA', 'SA', 'FG', 'UR', 'SR', 'CF', 'CY', 'AP', 'CJ', 'RM', 'OI', 'PK', 'SF',
                      'SM', 'SI']
    rep_data = []
    for v in sorted_variety:
        rep_data.append(ret_data.get(v, nan_spot))

    query_date = rep_data[0]['date']
    return {'code': r_status.SUCCESS, 'message': '分析期现数据成功!', 'query_date': query_date, 'data': rep_data}


@spot_api.get('/basis/', summary='指定日期现货基差分析')
async def spot_basis_analysis(date: datetime.datetime = Depends(verify_date)):
    query_ts = date.timestamp()
    # 查出指定日期和指定日期上一交易日的主力合约数据
    dominant_sql = 'SELECT a.quotes_ts as date_ts,b.variety_name,a.variety_en,a.contract,a.close_price,' \
                   'b.category,b.exchange ' \
                   'FROM dat_futures_price_position AS a ' \
                   'INNER JOIN sys_variety AS b ON a.variety_en=b.variety_en ' \
                   'WHERE a.quotes_ts<=%s AND a.variety_en=a.contract AND ' \
                   'a.quotes_ts>=(SELECT MAX(quotes_ts) FROM dat_futures_price_position WHERE quotes_ts<%s);'
    db = FAConnection(conn_name='QueryBasisDominant')
    records = db.query(dominant_sql, [int(query_ts), int(query_ts)], keep_conn=True)
    quotes_df = pd.DataFrame(records)
    if quotes_df.empty:
        db.close()
        return {'code': r_status.SUCCESS, 'message': '没有主力合约数据!', 'data': []}

    # 得到主力合约数据的日期最值
    min_quotes_ts, max_quotes_ts = quotes_df['date_ts'].min(), quotes_df['date_ts'].max()
    # 查出指定日期的现货报价数据
    spot_sql = 'SELECT spot_ts as date_ts,variety_en,price ' \
               'FROM dat_spot_price ' \
               'WHERE spot_ts=%s OR spot_ts=%s;'
    spot_records = db.query(spot_sql, [min_quotes_ts, max_quotes_ts])
    spot_df = pd.DataFrame(spot_records)
    spot_df = spot_df if spot_df.empty else spot_df[spot_df['price'] > 0]
    if spot_df.empty:
        nan_values = ['-' for _ in range(quotes_df.shape[0])]
        quotes_df['price'] = nan_values
        quotes_df['spot_zd'] = nan_values
        quotes_df['basis'] = nan_values
        quotes_df['basis_zd'] = nan_values
        quotes_df['basic_rate'] = nan_values
        quotes_df['date'] = quotes_df['date_ts'].apply(lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))
        rep_data = quotes_df.to_dict(orient='records')
        return {'code': r_status.SUCCESS, 'message': '获取基差数据成功!', 'data': rep_data,
                'query_date': date.strftime('%Y-%m-%d')}

    # 合并数据
    cul_df = pd.merge(quotes_df, spot_df, on=['date_ts', 'variety_en'], how='left')

    cul_df.sort_values(by=['variety_en', 'date_ts'], inplace=True)
    ret_df = pd.DataFrame(columns=['date_ts', 'variety_name', 'variety_en', 'contract', 'close_price', 'price',
                                   'spot_zd', 'basis', 'basis_zd', 'basic_rate'])
    for variety in cul_df.groupby(by=['variety_en']).groups:
        v_df = cul_df[(cul_df['variety_en'] == variety)]
        v_df.sort_values(by=['date_ts'], inplace=True)
        v_df['spot_zd'] = v_df['price'] - v_df['price'].shift(1)  # 现货涨跌
        v_df['basis'] = v_df['price'] - cul_df['close_price']  # 基差
        v_df['basis_zd'] = v_df['basis'] - v_df['basis'].shift(1)  # 基差涨跌
        v_df['basic_rate'] = v_df['basis'] / v_df['close_price']  # 基差率 = 基差/期货收盘价
        ret_df = pd.concat([ret_df, v_df])

    ret_df['date'] = ret_df['date_ts'].apply(lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))

    ret_df = ret_df[ret_df['date_ts'] == int(date.timestamp())]
    if ret_df.empty:
        return {'code': r_status.SUCCESS, 'message': '获取基差数据成功!', 'data': [],
                'query_date': date.strftime('%Y-%m-%d')}

    # 处理小数
    ret_df['close_price'] = ret_df['close_price'].apply(lambda x: round(x, 2))
    ret_df['price'] = ret_df['price'].apply(lambda x: round(x, 2))
    ret_df['spot_zd'] = ret_df['spot_zd'].apply(lambda x: round(x, 2))
    ret_df['basis'] = ret_df['basis'].apply(lambda x: round(x, 2))
    ret_df['basis_zd'] = ret_df['basis_zd'].apply(lambda x: round(x, 2))
    ret_df['basic_rate'] = ret_df['basic_rate'].apply(lambda x: round(x, 4))

    # 基差率排序
    ret_df.sort_values(by=['basic_rate'], ascending=False, inplace=True)

    ret_df.fillna('-', inplace=True)
    ret_data = {item['variety_en']: item for item in ret_df.to_dict(orient='records')}

    exists_variety = ret_df['variety_en'].to_list()

    # (控制品种)
    nan_spot = {'date': '-', 'variety_name': '-', 'price': '-', 'spot_zd': '-', 'basis': '-', 'basis_zd': '-',
                'basic_rate': '-'}
    _variety = ['NI', 'CU', 'ZN', 'PB', 'AL', 'SN', 'AU', 'AG', 'SS', 'RB', 'HC', 'BU', 'RU', 'SP', 'A', 'M', 'Y',
                'P', 'LH', 'JD', 'C', 'CS', 'I', 'J', 'JM', 'SC', 'FU', 'LU', 'L', 'PP', 'V', 'EB', 'PG', 'EG',
                'TA', 'PF', 'MA', 'SA', 'FG', 'UR', 'SR', 'CF', 'CY', 'CJ', 'RM', 'OI', 'PK', 'ZC', 'SF',
                'SM']

    rep_data = []
    for v in exists_variety:
        if v in _variety:
            rep_data.append(ret_data.get(v, nan_spot))
    for _v in _variety:
        if _v not in exists_variety:
            rep_data.append(nan_spot)

    return {'code': r_status.SUCCESS, 'message': '获取基差数据成功!', 'data': rep_data,
            'query_date': date.strftime('%Y-%m-%d')}


@spot_api.get('/latest/', summary='获取最新日期现货报价和涨跌数据')
async def get_latest_spot_price():
    sql = 'SELECT a.spot_ts,b.variety_name,a.variety_en,a.price,a.src_note FROM dat_spot_price AS a ' \
          'INNER JOIN sys_variety AS b ON a.variety_en=b.variety_en ' \
          'WHERE a.spot_ts>=' \
          '(SELECT MAX(spot_ts) FROM dat_spot_price WHERE spot_ts<(SELECT MAX(spot_ts) FROM dat_spot_price));'
    db = FAConnection(conn_name='最新现货报价')
    records = db.query(sql)
    df = pd.DataFrame(records)
    df = df[df['price'] > 0]
    if df.empty:
        return {'code': r_status.SUCCESS, 'message': '获取现货报价数据成功!', 'data': []}
    # 品种分组,并排序计算价格涨跌
    ret_df = pd.DataFrame(columns=['spot_ts', 'variety_name', 'variety_en', 'price', 'spot_zd', 'src_note'])
    for variety in df.groupby(by=['variety_en']).groups:
        v_df = df[df['variety_en'] == variety]
        v_df.sort_values(by=['spot_ts'], inplace=True)
        v_df['spot_zd'] = v_df['price'] - v_df['price'].shift(1)
        ret_df = pd.concat([ret_df, v_df])
    # 取日期大的数据
    ret_df = ret_df[ret_df['spot_ts'] == ret_df['spot_ts'].max()]

    ret_df['spot_zd'] = ret_df['spot_zd'].apply(lambda x: round(x, 2))
    ret_df['spot_date'] = ret_df['spot_ts'].apply(lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))
    ret_df.fillna(0, inplace=True)
    rep_data = ret_df.to_dict(orient='records')
    # print(ret_df[ret_df['spot_zd'] == '-'])
    spot_date = rep_data[0]['spot_date']
    return {'code': r_status.SUCCESS, 'message': '获取现货报价数据成功!', 'data': rep_data, 'spot_date': spot_date}
